package uk.ac.sanger.das.dasregistry.springrest.coordinates;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.biodas.jdas.dassources.utils.DasCoordinateSystemCollector;

import org.biodas.jdas.schema.registrycoordinates.DASCOORDINATESYSTEM;
import org.biodas.jdas.schema.sources.COORDINATES;
import org.biodas.jdas.util.CoordinateUtil;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.jdbc.support.rowset.SqlRowSet;

public class DasCoordintesDaoSangerRegistryJDAS extends SimpleJdbcDaoSupport
		implements CoordinatesDao {

	public static final String COORDINATE_ROOT_URI = "http://www.dasregistry.org/dasregistry/coordsys/";//the uri as specified for each coord
	DasCoordinateSystemCollector coordmap;
	List<COORDINATES> coordinates;
	DASCOORDINATESYSTEM coordSysObject;

	public DASCOORDINATESYSTEM getAllCoordinates() {

		if (coordinates == null) {
			coordinates = new ArrayList();
			coordmap = this.getAllCoordinateSystemsMap();
			Set<Integer> set = coordmap.keySet();
			for (Integer key : set) {
				COORDINATES coord = coordmap.get(key);
				coordinates.add(coord);
				coordSysObject = new DASCOORDINATESYSTEM();
				coordSysObject.getCOORDINATES().addAll(coordinates);
			}
		}

		return coordSysObject;
	}

	public DasCoordinateSystemCollector getAllCoordinateSystemsMap() {
		// System.out.println("DasRegistrySql: getAllCoordinateSystemsMap ");

		if (coordmap == null) {
			coordmap = new DasCoordinateSystemCollector();
		}
		String sql = "select cs_names.coordinateSystem, category.category, ncbi_tax_id, "
				+ " coordNS_auto,coordNS.prefix, cs_names.version "
				+ " from coordNS, cs_names, category "
				+ " where coordNS.coord_auto = cs_names.coord_auto and "
				+ " coordNS.category_auto = category.category_auto "
				+ " order by coordNS.coord_auto";
		// System.out.println(sql);
		SqlRowSet rowSet = getJdbcTemplate().queryForRowSet(sql);

		while (rowSet.next()) {
			int coordNS_auto = rowSet.getInt(4);
			COORDINATES dcs = getCoordinateSystemFromRow(rowSet);
			// System.out.println("new DAS coordsys " + dcs);
			// System.out.println(uniqueCsName);
			// add coordinate system to map
			coordmap.addCoordinateSystem(coordNS_auto, dcs);

		}

		return coordmap;
	}

	private COORDINATES getCoordinateSystemFromRow(SqlRowSet rowSet) {
		COORDINATES dcs = new COORDINATES();
		String cs = rowSet.getString(1);
		dcs.setAuthority(cs);
		String ca = rowSet.getString(2);
		dcs.setSource(ca);
		int taxid = rowSet.getInt(3);
		if(taxid!=0){
		dcs.setTaxid(String.valueOf(taxid));
		}
		int coordNS_auto = rowSet.getInt(4);
		String version = rowSet.getString(6);

		if (version != null && !version.equals("null") && !version.equals("")) {
			dcs.setVersion(version);
		} else {

		}

		String sqltax = " SELECT preferred_name from organism where taxid = ?";
		logger.debug("taxid=" + taxid);
		// single result here
		Map<String, Object> organismMap = getJdbcTemplate().queryForMap(sqltax,
				taxid);

		String organism = (String) organismMap.get("preferred_name");
		logger.debug("organism=" + organism);
		if (version != null){
			cs += "_" + version;
		}
		String csString = cs + "," + ca;
		if (!organism.equals("")&& !organism.equals("any")) {
			csString += "," + organism;
		}

		if (ca.equals(null))
			System.out.println("ca=" + ca);

		dcs.setContent(csString);
		String uniqueCsName = COORDINATE_ROOT_URI+"CS_"
				+ "DS" + coordNS_auto;
		dcs.setUri(uniqueCsName);

		return dcs;

	}

	@Override
	public DASCOORDINATESYSTEM add(COORDINATES cs) throws DasCoordinateNotRegisterableExcepton {
		Connection conn = null;
		List<COORDINATES> createdDcs = new ArrayList<COORDINATES>();

		try {

			try {
				conn = this.getDataSource().getConnection();
			} catch (SQLException e) {
				//  Auto-generated catch block
				e.printStackTrace();
			}
							// Basic tests for required fields:
				if (cs.getAuthority() == null || cs.getAuthority().equals("")) {
					throw new DasCoordinateNotRegisterableExcepton(
							"Coordinate system name/authority is required");
				}
				if (cs.getSource() == null || cs.getSource().equals("")) {
					throw new DasCoordinateNotRegisterableExcepton(
							"Coordinate system source/type is required");
				}

				// Create the coordinate system name (e.g. "NCBI_36") if
				// necessary
				int csNameId = 0;
				try {
					csNameId = getCoordAuto(conn,
							cs.getAuthority(), cs.getVersion());
				} catch (SQLException e) {
					//  Auto-generated catch block
					e.printStackTrace();
				}
				if (csNameId < 1) {
					try {
						csNameId = createNewCsName(conn,
								cs.getAuthority(), cs.getVersion());
					} catch (SQLException e) {
						//  Auto-generated catch block
						e.printStackTrace();
					}
				}

				
				 
				int ncbiId = Integer.valueOf(cs.getTaxid());
				String orgName = CoordinateUtil.getOrganismName(cs);
				// If the coordinate system is species specific (use the taxid
				// as the primary source):
				if (ncbiId > 0) {

					// Check the validity of the taxonomy ID
					String knownOrgName = null;
					try {
						knownOrgName = getOrganismByNCBITaxId(conn, ncbiId);
					} catch (SQLException e) {
						//  Auto-generated catch block
						e.printStackTrace();
					}
					if (knownOrgName == null) {
						throw new DasCoordinateNotRegisterableExcepton(
								"Unknown organism with taxonomy ID: " + ncbiId);
					}

				} else if (orgName != null && !orgName.equals("")) {
					try {
						ncbiId = getNCBITaxId(conn,
								orgName);
					} catch (SQLException e) {
						//  Auto-generated catch block
						e.printStackTrace();
					} // Throws
					// exception
					// if
					// not
					// found,
					// but
					// test
					// just
					// in
					// case
					if (ncbiId < 1) {
						throw new DasCoordinateNotRegisterableExcepton("Unknown organism: "
								+ orgName);
					}
				}
				// otherwise this coordinate system is not species-specific

				int categoryId = 0;
				try {
					categoryId = getCategoryAuto(conn,
							cs.getSource());
				} catch (SQLException e) {
					//  Auto-generated catch block
					e.printStackTrace();
				}
				if (categoryId < 1) {
					throw new DasCoordinateNotRegisterableExcepton(
							"Unknown coordinate system category: "
									+ cs.getSource());
				}

				COORDINATES exists = null;
				try {
					exists = getCoordinateSystem(conn, csNameId, categoryId, ncbiId);
				} catch (SQLException e) {
					//  Auto-generated catch block
					e.printStackTrace();
                                        throw new DasCoordinateNotRegisterableExcepton("error in sql when adding coordinate system "+e.getLocalizedMessage());
				}

				// test in here to see that it doensn't exist already then test
				// in the database
				List<COORDINATES> coords = this.getAllCoordinates().getCOORDINATES();
				
				cs.setTaxid(Integer.toString(ncbiId));

				System.out.println(cs);
				for (COORDINATES coord : coords) {
					if (cs.equals(coord)) {
						System.out.println("coord found=" + cs);
						//dcs.remove(cs);
						logger.debug("das coord already exists with uri="+coord.getUri());
						return null;
					}
				}

				if (exists == null) {
					int coordSysId = 0;
					try {
						coordSysId = createCoordSys(conn,
								csNameId, categoryId, ncbiId, null);
					} catch (SQLException e) {
						//  Auto-generated catch block
						e.printStackTrace();
					}
					try {
						exists = getCoordinateSystem(conn,
								coordSysId);
					} catch (SQLException e) {
						//  Auto-generated catch block
						e.printStackTrace();
					}
					if (exists == null) {
						throw new DasCoordinateNotRegisterableExcepton(
								"Unable to create coordinate system");
					}
				}
                                //we don't rely on finally as there is no gaurantee when it will be called
                                if (conn != null) {
				try {
					conn.close();
				} catch (SQLException es) {
					es.printStackTrace();
				}
			}
                                
				DASCOORDINATESYSTEM newC=new DASCOORDINATESYSTEM();
				newC.getCOORDINATES().add(exists);
				this.modified();
				return newC;

			

		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException es) {
					es.printStackTrace();
				}
			}
		}
		
	}


	/**
	 * get the coord_auto from cs_names table
	 * 
	 * @param conn
	 * @param name
	 * @param version
	 * @return the coord_auto field for the coordsys name
	 * @throws SQLException
	 */

	public int getCoordAuto(Connection conn, String name, String version)
			throws SQLException {

		String sql;
		if (version != null) {
			sql = "SELECT coord_auto from cs_names where coordinateSystem = ? and version = ?";
		} else {
			sql = "SELECT coord_auto from cs_names where coordinateSystem = ? ";
		}

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, name);
		if (version != null)
			ps.setString(2, version);

		ResultSet rs = ps.executeQuery();
		int id = -1;
		if (rs.next()) {
			id = rs.getInt(1);
		}
		rs.close();
		ps.close();
		return id;
	}
	
	public static int getLastInsertId(Statement stmt) throws SQLException {
		ResultSet rs = null;
		if (stmt != null) {
			rs = stmt.getGeneratedKeys();
		}

		int id = -1;
		if (rs != null && rs.next()) {
			id = rs.getInt(1);
		}

		rs.close();
		if (id != -1)
			return id;

		throw new SQLException("No LastInsertId available");

	}
	
	// ** create a new coordinate system name */
	public int createNewCsName(Connection conn, String txt, String version)
			throws SQLException {
		if (txt.length() > 255) {
			throw new SQLException("name for coordinate system is too long!");
		}
		logger.debug("creating new cs name txt=" + txt + " version=" + version);
		String sql = "INSERT INTO cs_names (coordinateSystem, version ) values (?,?)";
		PreparedStatement ps = conn.prepareStatement(sql,java.sql.Statement.RETURN_GENERATED_KEYS );

		ps.setString(1, txt);
		ps.setString(2, version);
		ps.executeUpdate();
		// get last insert id..
		int id = getLastInsertId(ps);
		ps.close();
		return id;
	}
	
	/**
	 * get the name of an organism identified by its NCBI taxonomy ID
	 * 
	 * @param conn
	 * @param ncbiId
	 * @return the name of the organism according to the database, or null if
	 *         not found
	 * @throws SQLException
	 */
	public String getOrganismByNCBITaxId(Connection conn, int ncbiId)
			throws SQLException {

		String SELECTORGANISM = " SELECT preferred_name  from organism where taxid = ?";

		String organism = null;

		PreparedStatement selectorgs = conn.prepareStatement(SELECTORGANISM);
		selectorgs.setInt(1, ncbiId);
		ResultSet rs = selectorgs.executeQuery();
		if (rs.next()) {
			organism = rs.getString(1);
		}
		rs.close();
		selectorgs.close();
		return organism;
	}

	
	/**
	 * return the taxid for an organism
	 * 
	 * @param conn
	 * @param organism
	 * @return the NCBI taxonomy id
	 * @throws SQLException
	 */
	public int getNCBITaxId(Connection conn, String organism)
			throws SQLException {
		String sql = "select taxid from organism where preferred_name = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, organism);

		ResultSet rs = ps.executeQuery();
		int id = -1;
		if (rs.next()) {
			id = rs.getInt(1);
		}
		rs.close();
		ps.close();

		if (id != -1)
			return id;

		throw new SQLException("no organism >" + organism + "< found!");
	}
	
	/**
	 * rename the category_auto of the category or -1 if not know...
	 * 
	 * @param conn
	 * @param category
	 * @return the category_auto of the category or -1 if not found
	 * @throws SQLException
	 */
	public int getCategoryAuto(Connection conn, String category)
			throws SQLException {
		logger.debug("getCategoryAuto category=" + category);
		String sql = "select category_auto from category where category = ?";
		PreparedStatement ps = conn.prepareStatement(sql);

		ps.setString(1, category);

		ResultSet rs = ps.executeQuery();

		int cat = -1;
		if (rs.next()) {
			cat = rs.getInt(1);
		}

		rs.close();
		ps.close();
		return cat;

	}
	
	
	public COORDINATES getCoordinateSystem(Connection conn,
			int coord_auto, int category_auto, int tax_id) throws SQLException {
		COORDINATES coordSys = null;
		// System.out.println("XXXX in getCoordinAteSystem " + coordNS_auto);
		String sql = "select cs_names.coordinateSystem, category.category, ncbi_tax_id, "
				+ " coordNS_auto,coordNS.prefix, cs_names.version "
				+ " from coordNS, cs_names, category "
				+ " where coordNS.coord_auto = cs_names.coord_auto and "
				+ " coordNS.category_auto = category.category_auto "
				+ " and  coordNS.coord_auto = ?"
				+ " and  coordNS.category_auto = ?"
				+ " and  coordNS.ncbi_tax_id = ?";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, coord_auto);
		ps.setInt(2, category_auto);
		ps.setInt(3, tax_id);

		String sqltax = " SELECT preferred_name from organism where taxid = ?";
		PreparedStatement tax = conn.prepareStatement(sqltax);

		ResultSet row = ps.executeQuery();
		while (row.next()) {
			// System.out.println(" XXXX got " + row.getString(1));
			String prefix = row.getString(5);
			if (prefix == null)
				prefix = "DS"; //  get this from config file
			coordSys = getCoordinateSystemFromRow(row, tax, prefix);
		}
		row.close();
		ps.close();

		return coordSys;
	}
	
	private COORDINATES getCoordinateSystemFromRow(ResultSet row,
			PreparedStatement tax, String prefix) throws SQLException {

		String cs = row.getString(1);
		String ca = row.getString(2);
		int taxid = row.getInt(3);
		int coordNS_auto = row.getInt(4);
		String thisprefix = row.getString(5);
		String version = row.getString(6);

		String organism = "";
		if (taxid != 0) {
			tax.setInt(1, taxid);

			ResultSet taxres = tax.executeQuery();
			if (taxres.next()) {
				organism = taxres.getString(1);
			}
			taxres.close();
		}

		
		COORDINATES dcs = new COORDINATES();
//		String organism = (String) organismMap.get("preferred_name");
//		logger.debug("organism=" + organism);
//		if (version != null){
//			cs += "_" + version;
//		}
//		String csString = cs + "," + ca;
//		if (!organism.equals("")&& !organism.equals("any")) {
//			csString += "," + organism;
//		}
//
//		if (ca.equals(null))
//			System.out.println("ca=" + ca);
//
//		dcs.setContent(csString);
//		String uniqueCsName = COORDINATE_ROOT_URI+"CS_"
//				+ "DS" + coordNS_auto;
//		dcs.setUri(uniqueCsName);
		dcs.setTaxid(Integer.toString(taxid));
		if (ca.equals(null))
			System.out.println("ca=" + ca);

		dcs.setSource(ca);
		dcs.setAuthority(cs);
		dcs.setVersion(version);
				
		//set up the element content for the coordinatesystem
		if (version != null){
		cs += "_" + version;
	}
	String csString = cs + "," + ca;
	if (!organism.equals("")&& !organism.equals("any")) {
		csString += "," + organism;
	}

	if (ca.equals(null))
		System.out.println("ca=" + ca);

	dcs.setContent(csString);
	String uniqueCsName = COORDINATE_ROOT_URI+"CS_"
			+ "DS" + coordNS_auto;
	dcs.setUri(uniqueCsName);
		return dcs;

	}

	/**
	 * create a new coordinate system
	 * 
	 * @param conn
	 * @param coord_auto
	 * @param category_auto
	 * @param ncbi_tax_id
	 * @param prefix
	 * @return the auto-increment id produced by mysql
	 * @throws SQLException
	 */
	public int createCoordSys(Connection conn, int coord_auto,
			int category_auto, int ncbi_tax_id, String prefix)
			throws SQLException {

		logger.info("creating new coord sys " + coord_auto + " "
				+ category_auto + " " + ncbi_tax_id);

		String sqlNS = "insert into coordNS ( coord_auto, category_auto, ncbi_tax_id ,prefix)  values (?,?,?,?)";
		PreparedStatement ps = conn.prepareStatement(sqlNS, java.sql.Statement.RETURN_GENERATED_KEYS);

		ps.setInt(1, coord_auto);
		ps.setInt(2, category_auto);
		ps.setInt(3, ncbi_tax_id);
		ps.setString(4, prefix);
		ps.executeUpdate();
		int id = getLastInsertId(ps);
		ps.close();
		return id;

	}

	public COORDINATES getCoordinateSystem(Connection conn,
			int coordNS_auto) throws SQLException {
		COORDINATES coordSys = null;
		// System.out.println("XXXX in getCoordinAteSystem " + coordNS_auto);
		String sql = "select cs_names.coordinateSystem, category.category, ncbi_tax_id, "
				+ " coordNS_auto,coordNS.prefix, cs_names.version "
				+ " from coordNS, cs_names, category "
				+ " where coordNS.coord_auto = cs_names.coord_auto and "
				+ " coordNS.category_auto = category.category_auto "
				+ " and  coordNS.coordNS_auto = ?";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, coordNS_auto);

		String sqltax = " SELECT preferred_name from organism where taxid = ?";
		PreparedStatement tax = conn.prepareStatement(sqltax);

		ResultSet row = ps.executeQuery();
		while (row.next()) {
			// System.out.println(" XXXX got " + row.getString(1));
			String prefix = row.getString(5);
			if (prefix == null)
				prefix = "DS"; //  get this from config file
			coordSys = getCoordinateSystemFromRow(row, tax, prefix);
		}
		row.close();
		ps.close();

		return coordSys;
	}
	public java.util.Date getLastModified() {
		String sql = "select lastModified from admin where adid = 1";
		java.util.Date date = new Date(0);
		SqlRowSet rows = getJdbcTemplate().queryForRowSet(sql);

		while (rows.next()) {
			// System.out.println("adding label" + (String) row.get("label"));
			Timestamp t = rows.getTimestamp("lastModified");
			logger.debug("t=" + t.toString());

			if (t != null) {

				date = new java.util.Date(t.getTime());
			}

			return date;
		}
		return date;
	}
	
	/**
	 * tell the database the sources or coodinate system has changed
	 */
	private void modified() {
		logger.debug("modified now");
		String sql = "update admin set lastModified=now()";
		getJdbcTemplate().update(sql);

	}
}
